package excelFileReader;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

/**
 * @author Idan
 *This class is used to perform ETL on Presbia reports
 */
public class ExcelReaderOrdersPresbia2014
{
	private static Connection conn = null;
	private static ArrayList<Object> row = new ArrayList<Object>();
	private static String fileYear;
	public void processOneSheet(String filename) throws Exception {
		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();

		XMLReader parser = fetchSheetParser(sst);

		// rId2 found by processing the Workbook
		// Seems to either be rId# or rSheet#
		InputStream sheet2 = r.getSheet("rId2");
		InputSource sheetSource = new InputSource(sheet2);
		parser.parse(sheetSource);
		sheet2.close();
	}

	public void processAllSheets(String filename) throws Exception 
	{
		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();
		
		XMLReader parser = fetchSheetParser(sst);

		Iterator<InputStream> sheets = r.getSheetsData();
		while(sheets.hasNext())
		{
			System.out.println("Processing new sheet:\n");
			InputStream sheet = sheets.next();
			InputSource sheetSource = new InputSource(sheet);
			parser.parse(sheetSource);
			sheet.close();
			System.out.println("");
		}
	}

	public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException 
	{
		XMLReader parser =
			XMLReaderFactory.createXMLReader(
					"org.apache.xerces.parsers.SAXParser"
			);
		ContentHandler handler = new SheetHandler(sst);
		parser.setContentHandler(handler);
		return parser;
	}

	/** 
	 * See org.xml.sax.helpers.DefaultHandler javadocs 
	 */
	public static class SheetHandler extends DefaultHandler
	{
		private SharedStringsTable sst;
		private String lastContents;
		private boolean nextIsString;
		
		private SheetHandler(SharedStringsTable sst) 
		{
			this.sst = sst;
		}
		
		public void startElement(String uri, String localName, String name,
				Attributes attributes) throws SAXException 
				{
			// c => cell
			if(name.equals("c")) {
				// Print the cell reference
				System.out.print(attributes.getValue("r") + " - ");
				
				if ( attributes.getValue("r").startsWith("AE") ) // Test if we reached the last column
				{
					//row.add("null"); // Empty comment
				// Time to dump the line and start a new one
						System.out.print("NEW LINE!\n");
					
				}			
				
				
				// Figure out if the value is an index in the SST
				String cellType = attributes.getValue("t");
				if(cellType != null && cellType.equals("s"))
				{	
					nextIsString = true;
				} else {
					nextIsString = false;
				}
			}
			// Clear contents cache
			lastContents = "";
		}
		
		public void endElement(String uri, String localName, String name)
				throws SAXException
				{
			// Process the last contents as required.
			// Do now, as characters() may be called more than once
			if(nextIsString) {
				int idx = Integer.parseInt(lastContents);
				lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
				
				
            nextIsString = false;
			}

			// v => contents of a cell
			// Output after we've seen the string contents
			if(name.equals("v"))
			{
				row.add(lastContents); // Add value to row
				System.out.println("added " + lastContents);
			
			}
		}

		

		public void characters(char[] ch, int start, int length)
				throws SAXException
				{
			lastContents += new String(ch, start, length);
		}
	}
	

	
	
	public void test() throws SQLException
	{
		Statement stmt = null;
		ResultSet rs = null;
	    stmt = conn.createStatement();
	    rs = stmt.executeQuery("SELECT * FROM status");
	    
	    while(rs.next())
	    {
	    	
	    	System.out.println(rs.getString(1));
	    	System.out.println(rs.getString(2));

	    
	    }
	    
	    if (rs != null) {
	        try {
	            rs.close();
	        } catch (SQLException sqlEx) { } // ignore
	        rs = null;
	    }
	    if (stmt != null) {
	        try {
	            stmt.close();
	        } catch (SQLException sqlEx) { } // ignore
	        stmt = null;
	    }
	}
	
	public static void main(String[] args) throws Exception 
	{
		ExcelReaderOrdersPresbia2014 howto = new ExcelReaderOrdersPresbia2014();
		
		
		
		try
		{
		    conn = DriverManager.getConnection("jdbc:mysql://localhost/hanitdb?" +
		                                   "user=root&password=");

		    fileYear = "2014";
		    howto.processAllSheets("presbiaOrders2014.xlsx");
		   
		   

		}
		 catch (SQLException ex)
		 {
			    // handle any errors
			    System.out.println("SQLException: " + ex.getMessage());
			    System.out.println("SQLState: " + ex.getSQLState());
			    System.out.println("VendorError: " + ex.getErrorCode());
			    
			}
			 sendToDB(row); // commit to DB
			 System.out.println("\n============2014 DONE=============");
			
		 
	}

	@SuppressWarnings("deprecation")
	private static void sendToDB(ArrayList<Object> row) throws SQLException 
	{
		Statement stmt = conn.createStatement();
		//ResultSet rs = null;
	
	//	int result = stmt.executeUpdate("UPDATE hanitdb.orders SET orderID = '" + row.get(3) + "', type = '" + row.get(5) + "', diopter = '" + row.get(6) + "', orderDate = '" + row.get(0) + "', quantity = 0, statusID = 0");
		
		int i=0;
		boolean isDate;
		int result;
		String formattedDate = new String();
		
		while (i<(row.size()))
		
	
		{
			// test if the value is an Excel date or a String
			isDate = !((String)(row.get(i+7))).contains("/");
			Date date = null;
			if (isDate)
			{
				date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(Double.parseDouble((String)row.get(i+7)));
				date.setYear(Integer.parseInt(fileYear)-1900); // Set year 112 (2012), 113 (2013) or 114 (2014)
			}
			else
			{				
				formattedDate = (String)row.get(i+7);
				if (formattedDate.contains(fileYear)) // date format includes the year
				{
					try {
						date = new SimpleDateFormat("dd/MM/yyyy").parse(formattedDate);
					} catch (ParseException e) {

						e.printStackTrace();
					}
				}
				else // date format is only the day and month
				{
					try {
						date = new SimpleDateFormat("dd/MM/yyyy").parse(formattedDate+"/"+fileYear); // Append the year to the date
					} catch (ParseException e) {

						e.printStackTrace();
					}
				}
				
			}
			java.sql.Date dateDB = new java.sql.Date(date.getTime());	
	
			row.set(i+7, dateDB);
			System.out.println("Commiting: orderID = " + row.get(i+4) + " type = PRESBIA" + " diopter = " + row.get(i+3) + " quantity = " + row.get(i+5));
	
			result = stmt.executeUpdate("INSERT IGNORE INTO orders(orderID,type,diopter,orderDate,quantity,statusID) VALUES('" + row.get(i+4) + "','PRESBIA"  +"','" + row.get(i+3) + "','" + row.get(i+7) + "','" + row.get(i+5) + "','99');");

			
			
			if (result !=1)
			System.out.println("failed!");
			else
			System.out.println("Great success!");	
			
			i=i+31;

		}
		
		stmt.close();
	
}
}
